import os
import pandas as pd

class ExcelUtil:
    def __init__(self, origin_folder_path, result_folder_path):
        self.origin_folder_path = origin_folder_path
        self.result_folder_path = result_folder_path
        self.excel_files = [file for file in os.listdir(origin_folder_path) if file.endswith('.xlsx')]

    # 指定sheet名合并
    def merge_excel_with_sheetname(self, merged_file_name, sheet_name_to_merge):
        all_data = pd.DataFrame()
        for file in self.excel_files:
            file_path = os.path.join(self.origin_folder_path, file)
            data = pd.read_excel(file_path, sheet_name=sheet_name_to_merge)
            all_data = pd.concat([all_data, data], ignore_index=True)

        output_file = os.path.join(self.result_folder_path, f"{merged_file_name}.xlsx")
        with pd.ExcelWriter(output_file) as writer:
            all_data.to_excel(writer, index=False)
        return output_file

    # 寻找同sheet名的进行合并
    def merge_excel_with_same_sheetname(self, merged_file_name):
        merged_data = {}
        for file in self.excel_files:
            file_path = os.path.join(self.origin_folder_path, file)
            excel_data = pd.ExcelFile(file_path)
            sheets = excel_data.sheet_names
            for sheet in sheets:
                if sheet not in merged_data:
                    merged_data[sheet] = pd.DataFrame()
                data = pd.read_excel(file_path, sheet_name=sheet)
                merged_data[sheet] = pd.concat([merged_data[sheet], data], ignore_index=True)

        output_file = os.path.join(self.result_folder_path, f"{merged_file_name}.xlsx")
        with pd.ExcelWriter(output_file) as writer:
            for sheet_name, data in merged_data.items():
                data.to_excel(writer, sheet_name=sheet_name, index=False)
        return output_file



def merge_excel_with_sheetname_tool(origin_folder_path, result_folder_path, merged_file_name, sheet_name_to_merge):
    return ExcelUtil(origin_folder_path, result_folder_path).merge_excel_with_sheetname(merged_file_name, sheet_name_to_merge)


def merge_excel_with_same_sheetname_tool(origin_folder_path, result_folder_path, merged_file_name):
    return ExcelUtil(origin_folder_path, result_folder_path).merge_excel_with_same_sheetname(merged_file_name)